package repo // 数据仓库层，处理附件相关的数据库操作和文件存储逻辑

import (
	"bytes"
	"context"
	"crypto/md5"
	"fmt"
	"image"
	"io"
	"io/fs"
	"net/http"
	"path/filepath"
	"runtime" // 添加runtime包用于内存管理
	"strings"
	"time"

	"github.com/rs/zerolog/log"
	"github.com/sysadminsmedia/homebox/backend/internal/data/ent/group"
	"github.com/sysadminsmedia/homebox/backend/internal/sys/config"
	"github.com/sysadminsmedia/homebox/backend/pkgs/utils"
	"github.com/zeebo/blake3"

	"github.com/gen2brain/avif"
	"github.com/gen2brain/heic"
	"github.com/gen2brain/jpegxl"
	"github.com/gen2brain/webp"
	"golang.org/x/image/draw"

	"github.com/google/uuid"
	"github.com/sysadminsmedia/homebox/backend/internal/data/ent"
	"github.com/sysadminsmedia/homebox/backend/internal/data/ent/attachment"
	"github.com/sysadminsmedia/homebox/backend/internal/data/ent/item"

	"gocloud.dev/blob"
	_ "gocloud.dev/blob/azureblob"
	_ "gocloud.dev/blob/fileblob"
	_ "gocloud.dev/blob/gcsblob"
	_ "gocloud.dev/blob/memblob"
	_ "gocloud.dev/blob/s3blob"

	"gocloud.dev/pubsub"
	_ "gocloud.dev/pubsub/awssnssqs"
	_ "gocloud.dev/pubsub/azuresb"
	_ "gocloud.dev/pubsub/gcppubsub"
	_ "gocloud.dev/pubsub/kafkapubsub"
	_ "gocloud.dev/pubsub/mempubsub"
	_ "gocloud.dev/pubsub/natspubsub"
	_ "gocloud.dev/pubsub/rabbitpubsub"
)

// AttachmentRepo is a repository for Attachments table that links Items to their
// associated files while also specifying the type of the attachment.
// AttachmentRepo 附件仓库，负责附件数据的CRUD和缩略图生成
// 包含数据库连接、存储配置和消息发布订阅功能
type AttachmentRepo struct {
	db         *ent.Client      // 数据库客户端
	storage    config.Storage   // 存储配置
	pubSubConn string           // 发布订阅连接字符串
	thumbnail  config.Thumbnail // 缩略图配置
}

type (
	ItemAttachment struct {
		ID        uuid.UUID       `json:"id"`
		CreatedAt time.Time       `json:"createdAt"`
		UpdatedAt time.Time       `json:"updatedAt"`
		Type      string          `json:"type"`
		Primary   bool            `json:"primary"`
		Path      string          `json:"path"`
		Title     string          `json:"title"`
		MimeType  string          `json:"mimeType,omitempty"`
		Thumbnail *ent.Attachment `json:"thumbnail,omitempty"`
	}

	ItemAttachmentUpdate struct {
		ID      uuid.UUID `json:"-"`
		Type    string    `json:"type"`
		Title   string    `json:"title"`
		Primary bool      `json:"primary"`
	}

	ItemCreateAttachment struct {
		Title   string    `json:"title"`
		Content io.Reader `json:"content"`
	}
)

func ToItemAttachment(attachment *ent.Attachment) ItemAttachment {
	return ItemAttachment{
		ID:        attachment.ID,
		CreatedAt: attachment.CreatedAt,
		UpdatedAt: attachment.UpdatedAt,
		Type:      attachment.Type.String(),
		Primary:   attachment.Primary,
		Path:      attachment.Path,
		Title:     attachment.Title,
		MimeType:  attachment.MimeType,
		Thumbnail: attachment.QueryThumbnail().FirstX(context.Background()),
	}
}

func (r *AttachmentRepo) path(gid uuid.UUID, hash string) string {
	path := filepath.Join(r.storage.PrefixPath, gid.String(), "documents", hash)
	// 手动将Windows路径分隔符替换为正斜杠，避免底层存储编码
	path = strings.ReplaceAll(path, "\\", "/")
	log.Debug().Str("path", path).Msg("生成文件存储路径")
	return path
}

func (r *AttachmentRepo) GetConnString() string {
	if strings.HasPrefix(r.storage.ConnString, "file:///./") {
		dir, err := filepath.Abs(strings.TrimPrefix(r.storage.ConnString, "file:///./"))
		if err != nil {
			log.Err(err).Msg("failed to get absolute path for attachment directory")
			return r.storage.ConnString
		}
		// Replace backslashes with forward slashes for proper URL format
		dir = strings.ReplaceAll(dir, "\\", "/")
		return fmt.Sprintf("file:///%s?no_tmp_dir=true", dir)
	}
	return r.storage.ConnString
}

// Create 创建新附件
// 参数:
//   - ctx: 上下文
//   - itemID: 关联物品ID
//   - doc: 附件创建数据
//   - typ: 附件类型
//   - primary: 是否为主附件
//
// 返回值:
//   - *ent.Attachment: 创建的附件实体
//   - error: 创建过程中发生的错误
func (r *AttachmentRepo) Create(ctx context.Context, itemID uuid.UUID, doc ItemCreateAttachment, typ attachment.Type, primary bool) (*ent.Attachment, error) {
	// 开始数据库事务
	// 开始数据库事务
	tx, err := r.db.Tx(ctx)
	if err != nil {
		return nil, fmt.Errorf("failed to begin transaction: %w", err)
	}
	// 事务回滚保护
	defer func() {
		if v := recover(); v != nil {
			tx.Rollback()
			panic(v)
		}
	}()

	bldrId := uuid.New()

	// 在数据库中创建附件记录
	bldr := tx.Attachment.Create().
		SetID(bldrId).
		SetCreatedAt(time.Now()).
		SetUpdatedAt(time.Now()).
		SetType(typ).
		SetItemID(itemID).
		SetTitle(doc.Title)

	if typ == attachment.TypePhoto && primary {
		bldr = bldr.SetPrimary(true)
		err := r.db.Attachment.Update().
			Where(
				attachment.HasItemWith(item.ID(itemID)),
				attachment.IDNEQ(bldrId),
			).
			SetPrimary(false).
			Exec(ctx)
		if err != nil {
			log.Err(err).Msg("failed to remove primary from other attachments")
			err := tx.Rollback() // 出错时回滚事务
			if err != nil {
				return nil, fmt.Errorf("failed to rollback transaction: %w", err)
			}
			return nil, fmt.Errorf("failed to update primary status: %w", err)
		}
	} else if typ == attachment.TypePhoto {
		// Autoset primary to true if this is the first attachment
		// that is of type photo
		cnt, err := tx.Attachment.Query().
			Where(
				attachment.HasItemWith(item.ID(itemID)),
				attachment.TypeEQ(typ),
			).
			Count(ctx)
		if err != nil {
			log.Err(err).Msg("failed to count attachments")
			err := tx.Rollback()
			if err != nil {
				return nil, err
			}
			return nil, err
		}

		if cnt == 0 {
			bldr = bldr.SetPrimary(true)
		}
	}

	// Get the group ID for the item the attachment is being created for
	itemGroup, err := tx.Item.Query().QueryGroup().Where(group.HasItemsWith(item.ID(itemID))).First(ctx)
	if err != nil {
		log.Err(err).Msg("failed to get item group")
		err := tx.Rollback()
		if err != nil {
			return nil, err
		}
		return nil, err
	}

	// 统一处理文件上传，图片类型也保存原图
	var finalPath string
	path, err := r.UploadFile(ctx, itemGroup, doc)
	if err != nil {
		err := tx.Rollback()
		if err != nil {
			return nil, err
		}
		return nil, err
	}
	finalPath = path

	// 设置MIME类型
	if typ == attachment.TypePhoto {
		// 图片类型根据实际文件内容设置MIME类型
		limitedReader := io.LimitReader(doc.Content, 1024*128)
		file, err := io.ReadAll(limitedReader)
		if err != nil {
			log.Err(err).Msg("读取文件内容失败")
			err = tx.Rollback()
			if err != nil {
				return nil, err
			}
			return nil, err
		}
		bldr = bldr.SetMimeType(http.DetectContentType(file[:min(512, len(file))]))
	} else {
		// 非图片类型按原逻辑处理
		limitedReader := io.LimitReader(doc.Content, 1024*128)
		file, err := io.ReadAll(limitedReader)
		if err != nil {
			log.Err(err).Msg("读取文件内容失败")
			err = tx.Rollback()
			if err != nil {
				return nil, err
			}
			return nil, err
		}
		bldr = bldr.SetMimeType(http.DetectContentType(file[:min(512, len(file))]))
	}

	bldr = bldr.SetPath(finalPath)

	attachmentDb, err := bldr.Save(ctx)
	if err != nil {
		log.Err(err).Msg("failed to save attachment to database")
		err = tx.Rollback()
		if err != nil {
			return nil, err
		}
		return nil, err
	}

	// 提交事务
	if err := tx.Commit(); err != nil {
		log.Err(err).Msg("failed to commit transaction")
		return nil, err
	}

	// 图片类型启用异步缩略图生成
	if typ == attachment.TypePhoto && r.thumbnail.Enabled {
		// 发送缩略图生成消息到pubsub主题
		pubsubString, err := utils.GenerateSubPubConn(r.pubSubConn, "thumbnails")
		if err != nil {
			log.Err(err).Msg("生成pubsub连接字符串失败")
			return nil, err
		}
		topic, err := pubsub.OpenTopic(ctx, pubsubString)
		if err != nil {
			log.Err(err).Msg("打开pubsub主题失败")
			return nil, err
		}

		err = topic.Send(ctx, &pubsub.Message{
			Body: []byte(fmt.Sprintf("attachment_created:%s", attachmentDb.ID.String())),
			Metadata: map[string]string{
				"group_id":      itemGroup.ID.String(),
				"attachment_id": attachmentDb.ID.String(),
				"title":         doc.Title,
				"path":          attachmentDb.Path,
			},
		})
		if err != nil {
			log.Err(err).Msg("failed to send message to topic")
			return nil, err
		}
	}

	return attachmentDb, nil
}

// Get 根据ID获取附件
// 参数:
//   - ctx: 上下文
//   - gid: 组ID
//   - id: 附件ID
//
// 返回值:
//   - *ent.Attachment: 附件实体
//   - error: 查询过程中发生的错误
func (r *AttachmentRepo) Get(ctx context.Context, gid uuid.UUID, id uuid.UUID) (*ent.Attachment, error) {
	// 查询数据库获取附件
	first, err := r.db.Attachment.Query().Where(attachment.ID(id)).Only(ctx)
	if err != nil {
		return nil, err
	}
	if first.Type == attachment.TypeThumbnail {
		// If the attachment is a thumbnail, get the parent attachment and check if it belongs to the specified group
		return r.db.Attachment.
			Query().
			Where(attachment.ID(id),
				attachment.HasThumbnailWith(attachment.HasItemWith(item.HasGroupWith(group.ID(gid)))),
			).
			WithItem().
			WithThumbnail().
			Only(ctx)
	} else {
		// For regular attachments, check if the attachment's item belongs to the specified group
		return r.db.Attachment.
			Query().
			Where(attachment.ID(id),
				attachment.HasItemWith(item.HasGroupWith(group.ID(gid))),
			).
			WithItem().
			WithThumbnail().
			Only(ctx)
	}
}

// Update 更新附件信息
// 参数:
//   - ctx: 上下文
//   - gid: 组ID
//   - id: 附件ID
//   - data: 附件更新数据
//
// 返回值:
//   - *ent.Attachment: 更新后的附件实体
//   - error: 更新过程中发生的错误
func (r *AttachmentRepo) Update(ctx context.Context, gid uuid.UUID, id uuid.UUID, data *ItemAttachmentUpdate) (*ent.Attachment, error) {
	// 验证附件是否属于指定组
	_, err := r.db.Attachment.Query().
		Where(
			attachment.ID(id),
			attachment.HasItemWith(item.HasGroupWith(group.ID(gid))),
		).
		Only(ctx)
	if err != nil {
		return nil, err
	}

	// TODO: execute within Tx
	typ := attachment.Type(data.Type)

	bldr := r.db.Attachment.UpdateOneID(id).
		SetType(typ)

	// Primary only applies to photos
	if typ == attachment.TypePhoto {
		bldr = bldr.SetPrimary(data.Primary)
	} else {
		bldr = bldr.SetPrimary(false)
	}

	updatedAttachment, err := bldr.Save(ctx)
	if err != nil {
		return nil, err
	}

	attachmentItem, err := updatedAttachment.QueryItem().Only(ctx)
	if err != nil {
		return nil, err
	}

	// Ensure all other attachments are not primary
	err = r.db.Attachment.Update().
		Where(
			attachment.HasItemWith(item.ID(attachmentItem.ID)),
			attachment.IDNEQ(updatedAttachment.ID),
		).
		SetPrimary(false).
		Exec(ctx)
	if err != nil {
		return nil, err
	}

	return r.Get(ctx, gid, updatedAttachment.ID)
}

// Delete 删除附件
// 参数:
//   - ctx: 上下文
//   - gid: 组ID
//   - itemId: 物品ID
//   - id: 附件ID
//
// 返回值:
//   - error: 删除过程中发生的错误
func (r *AttachmentRepo) Delete(ctx context.Context, gid uuid.UUID, itemId uuid.UUID, id uuid.UUID) error {
	// Validate that the attachment belongs to the specified group
	doc, err := r.db.Attachment.Query().
		Where(
			attachment.ID(id),
			attachment.HasItemWith(item.HasGroupWith(group.ID(gid))),
		).
		Only(ctx)
	if err != nil {
		return err
	}

	all, err := r.db.Attachment.Query().Where(attachment.Path(doc.Path)).All(ctx)
	if err != nil {
		return err
	}
	// If this is the last attachment for this path, delete the file
	if len(all) == 1 {
		thumb, err := doc.QueryThumbnail().First(ctx)
		if err != nil && !ent.IsNotFound(err) {
			log.Err(err).Msg("查询附件缩略图失败")
			return err
		}
		if thumb != nil {
			thumbBucket, err := blob.OpenBucket(ctx, r.GetConnString())
			if err != nil {
				log.Err(err).Msg("打开存储桶删除缩略图失败")
				return err
			}
			err = thumbBucket.Delete(ctx, thumb.Path)
			if err != nil {
				return err
			}
			_ = doc.Update().SetNillableThumbnailID(nil).SaveX(ctx)
			_ = thumb.Update().SetNillableThumbnailID(nil).SaveX(ctx)
			err = r.db.Attachment.DeleteOneID(thumb.ID).Exec(ctx)
			if err != nil {
				return err
			}
		}
		bucket, err := blob.OpenBucket(ctx, r.GetConnString())
		if err != nil {
			log.Err(err).Msg("打开存储桶失败")
			return err
		}
		defer func(bucket *blob.Bucket) {
			err := bucket.Close()
			if err != nil {
				log.Err(err).Msg("关闭存储桶失败")
			}
		}(bucket)
		err = bucket.Delete(ctx, doc.Path)
		if err != nil {
			return err
		}
	}

	return r.db.Attachment.DeleteOneID(id).Exec(ctx)
}

func (r *AttachmentRepo) Rename(ctx context.Context, gid uuid.UUID, id uuid.UUID, title string) (*ent.Attachment, error) {
	// Validate that the attachment belongs to the specified group
	_, err := r.db.Attachment.Query().
		Where(
			attachment.ID(id),
			attachment.HasItemWith(item.HasGroupWith(group.ID(gid))),
		).
		Only(ctx)
	if err != nil {
		return nil, err
	}

	return r.db.Attachment.UpdateOneID(id).SetTitle(title).Save(ctx)
}

// CreateThumbnail 为附件创建缩略图
// 参数:
//   - ctx: 上下文
//   - groupId: 组ID
//   - attachmentId: 附件ID
//   - title: 附件标题
//   - path: 附件路径
//
// 返回值:
//   - error: 缩略图创建过程中发生的错误
//
//nolint:gocyclo
func (r *AttachmentRepo) CreateThumbnail(ctx context.Context, groupId, attachmentId uuid.UUID, title string, path string) error {
	log.Debug().Msg("开始创建缩略图")
	
	// 检查是否已有缩略图（包括数据库记录和实际文件）
	originalAttachment, err := r.db.Attachment.Get(ctx, attachmentId)
	if err != nil {
		return fmt.Errorf("获取原附件失败: %w", err)
	}
	
	// 检查数据库中是否有缩略图记录
	thumbnail, err := originalAttachment.QueryThumbnail().Only(ctx)
	if err == nil && thumbnail != nil {
		// 检查实际缩略图文件是否存在
		bucket, err := blob.OpenBucket(ctx, r.GetConnString())
		if err == nil {
			_, err := bucket.NewReader(ctx, thumbnail.Path, nil)
			bucket.Close()
			if err == nil {
				// 数据库记录和实际文件都存在，跳过创建
				log.Info().
					Str("attachmentID", attachmentId.String()).
					Str("thumbnailPath", thumbnail.Path).
					Msg("缩略图已存在，跳过创建")
				return nil
			} else {
				// 数据库记录存在但文件不存在，删除无效记录
				log.Warn().
					Str("attachmentID", attachmentId.String()).
					Str("thumbnailPath", thumbnail.Path).
					Msg("缩略图文件不存在，将重新生成")
				
				// 删除无效的缩略图记录
				if err := r.db.Attachment.DeleteOne(thumbnail).Exec(ctx); err != nil {
					log.Err(err).Msg("删除无效缩略图记录失败")
				}
			}
		}
	}
	
	tx, err := r.db.Tx(ctx)
	if err != nil {
		return nil
	}
	// If there is an error during file creation rollback the database
	defer func() {
		if v := recover(); v != nil {
			err := tx.Rollback()
			if err != nil {
				return
			}
		}
	}()

	log.Debug().Msg("设置初始数据库事务")
	att := tx.Attachment.Create().
		SetID(uuid.New()).
		SetTitle(fmt.Sprintf("%s-thumb", title)).
		SetType(attachment.TypeThumbnail)

	log.Debug().Msg("打开原始文件")
	bucket, err := blob.OpenBucket(ctx, r.GetConnString())
	if err != nil {
		log.Err(err).Msg("failed to open bucket")
		err := tx.Rollback()
		if err != nil {
			return err
		}
		return err
	}
	defer func(bucket *blob.Bucket) {
		err := bucket.Close()
		if err != nil {
			err := tx.Rollback()
			if err != nil {
				return
			}
			log.Err(err).Msg("failed to close bucket")
		}
	}(bucket)

	origFile, err := bucket.Open(path)
	if err != nil {
		err := tx.Rollback()
		if err != nil {
			return err
		}
		return err
	}
	defer func(file fs.File) {
		err := file.Close()
		if err != nil {
			err := tx.Rollback()
			if err != nil {
				return
			}
			log.Err(err).Msg("failed to close file")
		}
	}(origFile)

	log.Debug().Msg("获取原始文件大小")
	stats, err := origFile.Stat()
	if err != nil {
		err := tx.Rollback()
		if err != nil {
			return err
		}
		log.Err(err).Msg("failed to stat original file")
		return err
	}

	if stats.Size() > 100*1024*1024 {
		return fmt.Errorf("original file %s is too large to create a thumbnail", title)
	}

	log.Debug().Msg("读取原始文件内容")
	contentBytes, err := io.ReadAll(origFile)
	if err != nil {
		err := tx.Rollback()
		if err != nil {
			return err
		}
		log.Err(err).Msg("failed to read original file content")
		return err
	}

	log.Debug().Msg("检测原始文件类型")
	contentType := http.DetectContentType(contentBytes[:min(512, len(contentBytes))])

	if contentType == "application/octet-stream" {
		if strings.HasSuffix(title, ".heic") || strings.HasSuffix(title, ".heif") {
			contentType = "image/heic"
		} else if strings.HasSuffix(title, ".avif") {
			contentType = "image/avif"
		}
	}

	switch {
	case isImageFile(contentType):
		log.Debug().Msg("为图片文件创建缩略图")
		img, _, err := image.Decode(bytes.NewReader(contentBytes))
		if err != nil {
			log.Err(err).Msg("failed to decode image file")
			err := tx.Rollback()
			if err != nil {
				log.Err(err).Msg("failed to rollback transaction")
				return err
			}
			return err
		}
		
		// 使用统一的缩略图格式
		bounds := img.Bounds()
		width := bounds.Max.X - bounds.Min.X
		height := bounds.Max.Y - bounds.Min.Y

		// 保持宽高比缩放
		maxSize := r.thumbnail.Width
		if width > height {
			if width > maxSize {
				height = height * maxSize / width
				width = maxSize
			}
		} else {
			if height > maxSize {
				width = width * maxSize / height
				height = maxSize
			}
		}

		// 创建缩略图
		thumbnail := image.NewRGBA(image.Rect(0, 0, width, height))
		draw.CatmullRom.Scale(thumbnail, thumbnail.Bounds(), img, bounds, draw.Over, nil)

		// 编码为WebP格式
		var buf bytes.Buffer
		if err := webp.Encode(&buf, thumbnail, webp.Options{Quality: 85}); err != nil {
			err := tx.Rollback()
			if err != nil {
				return err
			}
			return err
		}

		// 生成缩略图文件名
		thumbnailID := uuid.New()
		thumbnailPath := "attachments/" + thumbnailID.String() + "-thumb.webp"

		// 上传到存储
		bucket, err := blob.OpenBucket(ctx, r.GetConnString())
		if err != nil {
			log.Err(err).Msg("failed to open bucket")
			err := tx.Rollback()
			if err != nil {
				return err
			}
			return err
		}
		defer bucket.Close()

		if err := bucket.WriteAll(ctx, thumbnailPath, buf.Bytes(), &blob.WriterOptions{
			ContentType: "image/webp",
		}); err != nil {
			log.Err(err).Msg("failed to upload thumbnail")
			err := tx.Rollback()
			if err != nil {
				return err
			}
			return err
		}

		log.Debug().Msg("设置附件中的缩略图文件路径")
		att.SetPath(thumbnailPath)
	case contentType == "image/webp":
		log.Debug().Msg("creating thumbnail for webp file")
		img, err := webp.Decode(bytes.NewReader(contentBytes))
		if err != nil {
			log.Err(err).Msg("failed to decode webp image")
			err := tx.Rollback()
			if err != nil {
				return err
			}
			return err
		}
		
		// 使用统一的缩略图格式
		bounds := img.Bounds()
		width := bounds.Max.X - bounds.Min.X
		height := bounds.Max.Y - bounds.Min.Y

		// 保持宽高比缩放
		maxSize := r.thumbnail.Width
		if width > height {
			if width > maxSize {
				height = height * maxSize / width
				width = maxSize
			}
		} else {
			if height > maxSize {
				width = width * maxSize / height
				height = maxSize
			}
		}

		// 创建缩略图
		thumbnail := image.NewRGBA(image.Rect(0, 0, width, height))
		draw.CatmullRom.Scale(thumbnail, thumbnail.Bounds(), img, bounds, draw.Over, nil)

		// 编码为WebP格式
		var buf bytes.Buffer
		if err := webp.Encode(&buf, thumbnail, webp.Options{Quality: 85}); err != nil {
			err := tx.Rollback()
			if err != nil {
				return err
			}
			return err
		}

		// 生成缩略图文件名
		thumbnailID := uuid.New()
		thumbnailPath := "attachments/" + thumbnailID.String() + "-thumb.webp"

		// 上传到存储
		bucket, err := blob.OpenBucket(ctx, r.GetConnString())
		if err != nil {
			log.Err(err).Msg("failed to open bucket")
			err := tx.Rollback()
			if err != nil {
				return err
			}
			return err
		}
		defer bucket.Close()

		if err := bucket.WriteAll(ctx, thumbnailPath, buf.Bytes(), &blob.WriterOptions{
			ContentType: "image/webp",
		}); err != nil {
			log.Err(err).Msg("failed to upload thumbnail")
			err := tx.Rollback()
			if err != nil {
				return err
			}
			return err
		}

		log.Debug().Msg("setting thumbnail file path in attachment")
		att.SetPath(thumbnailPath)
	case contentType == "image/avif":
		log.Debug().Msg("creating thumbnail for avif file")
		img, err := avif.Decode(bytes.NewReader(contentBytes))
		if err != nil {
			log.Err(err).Msg("failed to decode avif image")
			err := tx.Rollback()
			if err != nil {
				return err
			}
			return err
		}
		
		// 使用统一的缩略图格式
		bounds := img.Bounds()
		width := bounds.Max.X - bounds.Min.X
		height := bounds.Max.Y - bounds.Min.Y

		// 保持宽高比缩放
		maxSize := r.thumbnail.Width
		if width > height {
			if width > maxSize {
				height = height * maxSize / width
				width = maxSize
			}
		} else {
			if height > maxSize {
				width = width * maxSize / height
				height = maxSize
			}
		}

		// 创建缩略图
		thumbnail := image.NewRGBA(image.Rect(0, 0, width, height))
		draw.CatmullRom.Scale(thumbnail, thumbnail.Bounds(), img, bounds, draw.Over, nil)

		// 编码为WebP格式
		var buf bytes.Buffer
		if err := webp.Encode(&buf, thumbnail, webp.Options{Quality: 85}); err != nil {
			err := tx.Rollback()
			if err != nil {
				return err
			}
			return err
		}

		// 生成缩略图文件名
		thumbnailID := uuid.New()
		thumbnailPath := "attachments/" + thumbnailID.String() + "-thumb.webp"

		// 上传到存储
		bucket, err := blob.OpenBucket(ctx, r.GetConnString())
		if err != nil {
			log.Err(err).Msg("failed to open bucket")
			err := tx.Rollback()
			if err != nil {
				return err
			}
			return err
		}
		defer bucket.Close()

		if err := bucket.WriteAll(ctx, thumbnailPath, buf.Bytes(), &blob.WriterOptions{
			ContentType: "image/webp",
		}); err != nil {
			log.Err(err).Msg("failed to upload thumbnail")
			err := tx.Rollback()
			if err != nil {
				return err
			}
			return err
		}

		log.Debug().Msg("setting thumbnail file path in attachment")
		att.SetPath(thumbnailPath)
	case contentType == "image/heic" || contentType == "image/heif":
		log.Debug().Msg("creating thumbnail for heic file")
		img, err := heic.Decode(bytes.NewReader(contentBytes))
		if err != nil {
			log.Err(err).Msg("failed to decode avif image")
			err := tx.Rollback()
			if err != nil {
				return err
			}
			return err
		}
		
		// 使用统一的缩略图格式
		bounds := img.Bounds()
		width := bounds.Max.X - bounds.Min.X
		height := bounds.Max.Y - bounds.Min.Y

		// 保持宽高比缩放
		maxSize := r.thumbnail.Width
		if width > height {
			if width > maxSize {
				height = height * maxSize / width
				width = maxSize
			}
		} else {
			if height > maxSize {
				width = width * maxSize / height
				height = maxSize
			}
		}

		// 创建缩略图
		thumbnail := image.NewRGBA(image.Rect(0, 0, width, height))
		draw.CatmullRom.Scale(thumbnail, thumbnail.Bounds(), img, bounds, draw.Over, nil)

		// 编码为WebP格式
		var buf bytes.Buffer
		if err := webp.Encode(&buf, thumbnail, webp.Options{Quality: 85}); err != nil {
			err := tx.Rollback()
			if err != nil {
				return err
			}
			return err
		}

		// 生成缩略图文件名
		thumbnailID := uuid.New()
		thumbnailPath := "attachments/" + thumbnailID.String() + "-thumb.webp"

		// 上传到存储
		bucket, err := blob.OpenBucket(ctx, r.GetConnString())
		if err != nil {
			log.Err(err).Msg("failed to open bucket")
			err := tx.Rollback()
			if err != nil {
				return err
			}
			return err
		}
		defer bucket.Close()

		if err := bucket.WriteAll(ctx, thumbnailPath, buf.Bytes(), &blob.WriterOptions{
			ContentType: "image/webp",
		}); err != nil {
			log.Err(err).Msg("failed to upload thumbnail")
			err := tx.Rollback()
			if err != nil {
				return err
			}
			return err
		}

		log.Debug().Msg("setting thumbnail file path in attachment")
		att.SetPath(thumbnailPath)
	case contentType == "image/jxl":
		log.Debug().Msg("creating thumbnail for jpegxl file")
		img, err := jpegxl.Decode(bytes.NewReader(contentBytes))
		if err != nil {
			log.Err(err).Msg("failed to decode avif image")
			err := tx.Rollback()
			if err != nil {
				return err
			}
			return err
		}
		
		// 使用统一的缩略图格式
		bounds := img.Bounds()
		width := bounds.Max.X - bounds.Min.X
		height := bounds.Max.Y - bounds.Min.Y

		// 保持宽高比缩放
		maxSize := r.thumbnail.Width
		if width > height {
			if width > maxSize {
				height = height * maxSize / width
				width = maxSize
			}
		} else {
			if height > maxSize {
				width = width * maxSize / height
				height = maxSize
			}
		}

		// 创建缩略图
		thumbnail := image.NewRGBA(image.Rect(0, 0, width, height))
		draw.CatmullRom.Scale(thumbnail, thumbnail.Bounds(), img, bounds, draw.Over, nil)

		// 编码为WebP格式
		var buf bytes.Buffer
		if err := webp.Encode(&buf, thumbnail, webp.Options{Quality: 85}); err != nil {
			err := tx.Rollback()
			if err != nil {
				return err
			}
			return err
		}

		// 生成缩略图文件名
		thumbnailID := uuid.New()
		thumbnailPath := "attachments/" + thumbnailID.String() + "-thumb.webp"

		// 上传到存储
		bucket, err := blob.OpenBucket(ctx, r.GetConnString())
		if err != nil {
			log.Err(err).Msg("failed to open bucket")
			err := tx.Rollback()
			if err != nil {
				return err
			}
			return err
		}
		defer bucket.Close()

		if err := bucket.WriteAll(ctx, thumbnailPath, buf.Bytes(), &blob.WriterOptions{
			ContentType: "image/webp",
		}); err != nil {
			log.Err(err).Msg("failed to upload thumbnail")
			err := tx.Rollback()
			if err != nil {
				return err
			}
			return err
		}

		log.Debug().Msg("setting thumbnail file path in attachment")
		att.SetPath(thumbnailPath)
	default:
		return fmt.Errorf("file type %s is not supported for thumbnail creation or document thumnails disabled", title)
	}

	att.SetMimeType("image/webp")

	log.Debug().Msg("保存缩略图附件到数据库")
	newThumbnail, err := att.Save(ctx)
	if err != nil {
		return err
	}

	_, err = tx.Attachment.UpdateOneID(attachmentId).SetThumbnail(newThumbnail).Save(ctx)
	if err != nil {
		return err
	}

	log.Debug().Msg("完成缩略图创建事务")
	if err := tx.Commit(); err != nil {
		log.Err(err).Msg("failed to commit transaction")
		return nil
	}
	return nil
}

// isImageFile 判断文件类型是否为图片
func isImageFile(contentType string) bool {
	imageTypes := []string{
		"image/jpeg",
		"image/jpg",
		"image/png",
		"image/gif",
		"image/bmp",
		"image/tiff",
		"image/webp",
		"image/svg+xml",
	}
	for _, t := range imageTypes {
		if contentType == t {
			return true
		}
	}
	return false
}

// CreateMissingThumbnails 为指定组中所有缺失缩略图的附件创建缩略图
// 参数:
//   - ctx: 上下文
//   - groupId: 组ID
//
// 返回值:
//   - int: 创建的缩略图数量
//   - error: 缩略图创建过程中发生的错误
func (r *AttachmentRepo) CreateMissingThumbnails(ctx context.Context, groupId uuid.UUID) (int, error) {
	// 查询指定组中所有非缩略图类型的附件
	attachments, err := r.db.Attachment.Query().
		Where(
			attachment.HasItemWith(item.HasGroupWith(group.ID(groupId))), // 属于指定组
			attachment.TypeNEQ("thumbnail"),                              // 非缩略图类型
		).
		All(ctx)
	if err != nil {
		return 0, err
	}

	pubsubString, err := utils.GenerateSubPubConn(r.pubSubConn, "thumbnails")
	if err != nil {
		log.Err(err).Msg("failed to generate pubsub connection string")
	}
	topic, err := pubsub.OpenTopic(ctx, pubsubString)
	if err != nil {
		log.Err(err).Msg("failed to open pubsub topic")
	}

	count := 0
	for _, attachment := range attachments {
		if r.thumbnail.Enabled {
			if !attachment.QueryThumbnail().ExistX(ctx) {
				if count > 0 && count%100 == 0 {
					time.Sleep(2 * time.Second)
				}
				err = topic.Send(ctx, &pubsub.Message{
					Body: []byte(fmt.Sprintf("attachment_created:%s", attachment.ID.String())),
					Metadata: map[string]string{
						"group_id":      groupId.String(),
						"attachment_id": attachment.ID.String(),
						"title":         attachment.Title,
						"path":          attachment.Path,
					},
				})
				if err != nil {
					log.Err(err).Msg("failed to send message to topic")
					continue
				} else {
					count++
				}
			}
		}
	}

	return count, nil
}



// UploadFile 上传文件到存储
// 参数:
//   - ctx: 上下文
//   - itemGroup: 所属组
//   - doc: 附件创建数据
//
// 返回值:
//   - string: 文件存储路径
//   - error: 上传过程中发生的错误
func (r *AttachmentRepo) UploadFile(ctx context.Context, itemGroup *ent.Group, doc ItemCreateAttachment) (string, error) {
	log.Debug().Str("group_id", itemGroup.ID.String()).Msg("开始上传文件")
	// Prepare for the hashing of the file contents
	hashOut := make([]byte, 32)

	// Read all content into a buffer
	buf := new(bytes.Buffer)
	_, err := io.Copy(buf, doc.Content)
	if err != nil {
		log.Err(err).Msg("failed to read file content")
		return "", err
	}
	// Now the buffer contains all the data, use it for hashing
	contentBytes := buf.Bytes()

	// We use blake3 to generate a hash of the file contents, the group ID is used as context to ensure unique hashes
	// for the same file across different groups to reduce the chance of collisions
	// additionally, the hash can be used to validate the file contents if needed
	blake3.DeriveKey(itemGroup.ID.String(), contentBytes, hashOut)

	// Write the file to the blob storage bucket which might be a local file system or cloud storage
	bucket, err := blob.OpenBucket(ctx, r.GetConnString())
	if err != nil {
		log.Err(err).Msg("failed to open bucket")
		return "", err
	}
	defer func(bucket *blob.Bucket) {
		err := bucket.Close()
		if err != nil {
			log.Err(err).Msg("failed to close bucket")
		}
	}(bucket)
	md5hash := md5.New()
	_, err = md5hash.Write(contentBytes)
	if err != nil {
		log.Err(err).Msg("failed to generate MD5 hash for storage")
		return "", err
	}
	contentType := http.DetectContentType(contentBytes[:min(512, len(contentBytes))])
	options := &blob.WriterOptions{
		ContentType: contentType,
		ContentMD5:  md5hash.Sum(nil),
	}
	path := r.path(itemGroup.ID, fmt.Sprintf("%x", hashOut))
	log.Debug().Str("full_path", path).Msg("文件将被写入此路径")
	err = bucket.WriteAll(ctx, path, contentBytes, options)
	if err != nil {
		log.Err(err).Msg("failed to write file to bucket")
		return "", err
	}

	return path, nil
}

// MigrateOriginalToThumbnail 将原图附件迁移为缩略图附件
// 参数:
//   - ctx: 上下文
//   - groupID: 组ID，如果为空则处理所有组
//   - dryRun: 是否仅预览不实际执行
//
// 返回值:
//   - int: 成功迁移的数量
//   - int: 跳过的数量
//   - error: 迁移过程中发生的错误
func (r *AttachmentRepo) MigrateOriginalToThumbnail(ctx context.Context, groupID uuid.UUID, dryRun bool) (int, int, error) {
	log.Info().
		Bool("dryRun", dryRun).
		Str("groupID", groupID.String()).
		Msg("开始迁移原图附件为缩略图")

	// 查询需要迁移的附件
	query := r.db.Attachment.Query()
	if groupID != uuid.Nil {
		query = query.Where(attachment.HasItemWith(item.HasGroupWith(group.ID(groupID))))
	}

	// 只处理图片类型且没有缩略图的附件
	attachments, err := query.
		Where(
			attachment.TypeEQ(attachment.TypePhoto),
			attachment.HasThumbnailWith(), // 没有缩略图
		).
		WithItem(func(iq *ent.ItemQuery) {
			iq.WithGroup()
		}).
		All(ctx)
	if err != nil {
		return 0, 0, fmt.Errorf("查询附件失败: %w", err)
	}

	var migrated, skipped int
	const batchSize = 50 // 每批处理50个文件

	for i := 0; i < len(attachments); i += batchSize {
		end := i + batchSize
		if end > len(attachments) {
			end = len(attachments)
		}

		batch := attachments[i:end]
		log.Info().
			Int("batchStart", i+1).
			Int("batchEnd", end).
			Int("batchSize", len(batch)).
			Msg("处理批次")

		for _, attachment := range batch {
			select {
			case <-ctx.Done():
				log.Info().
					Int("migrated", migrated).
					Int("skipped", skipped).
					Msg("迁移被中断")
				return migrated, skipped, ctx.Err()
			default:
				// 继续处理
			}

			// 检查文件是否存在
			bucket, err := blob.OpenBucket(ctx, r.GetConnString())
			if err != nil {
				log.Err(err).Str("attachmentID", attachment.ID.String()).Msg("打开存储桶失败")
				continue
			}

			// 读取原文件
			reader, err := bucket.NewReader(ctx, attachment.Path, nil)
			if err != nil {
				log.Err(err).Str("attachmentID", attachment.ID.String()).Msg("读取原文件失败")
				bucket.Close()
				continue
			}

			// 检测文件类型
			content, err := io.ReadAll(reader)
			reader.Close()
			bucket.Close()

			if err != nil {
				log.Err(err).Str("attachmentID", attachment.ID.String()).Msg("读取文件内容失败")
				continue
			}

			contentType := http.DetectContentType(content[:min(512, len(content))])
			if !isImageFile(contentType) {
				log.Info().
					Str("attachmentID", attachment.ID.String()).
					Str("contentType", contentType).
					Msg("跳过非图片文件")
				skipped++
				continue
			}

			if dryRun {
				log.Info().
					Str("attachmentID", attachment.ID.String()).
					Str("title", attachment.Title).
					Msg("预览：将迁移此附件")
				migrated++
				continue
			}

			// 创建缩略图
			thumbnailPath, err := r.createThumbnailFromBytes(ctx, attachment.QueryItem().FirstX(ctx).QueryGroup().FirstX(ctx), content)
			if err != nil {
				log.Err(err).Str("attachmentID", attachment.ID.String()).Msg("创建缩略图失败")
				continue
			}

			// 更新数据库：删除原文件，更新路径和MIME类型
			err = func() error {
				tx, err := r.db.Tx(ctx)
				if err != nil {
					return fmt.Errorf("开启事务失败: %w", err)
				}
				defer tx.Rollback()

				// 删除原文件
				bucket, err := blob.OpenBucket(ctx, r.GetConnString())
				if err != nil {
					return fmt.Errorf("打开存储桶失败: %w", err)
				}
				defer bucket.Close()

				if err := bucket.Delete(ctx, attachment.Path); err != nil {
					log.Warn().Str("path", attachment.Path).Err(err).Msg("删除原文件失败，继续执行")
				}

				// 更新附件记录
				_, err = tx.Attachment.UpdateOne(attachment).
					SetPath(thumbnailPath).
					SetMimeType("image/webp").
					Save(ctx)
				if err != nil {
					return fmt.Errorf("更新附件记录失败: %w", err)
				}

				return tx.Commit()
			}()

			if err != nil {
				log.Err(err).Str("attachmentID", attachment.ID.String()).Msg("迁移失败")
				continue
			}

			migrated++
			log.Info().
				Str("attachmentID", attachment.ID.String()).
				Str("title", attachment.Title).
				Str("newPath", thumbnailPath).
				Msg("成功迁移附件为缩略图")
		}

		// 每批处理完后释放内存
		if !dryRun {
			runtime.GC()
		}
	}

	log.Info().
		Int("migrated", migrated).
		Int("skipped", skipped).
		Int("total", len(attachments)).
		Msg("迁移完成")

	return migrated, skipped, nil
}

// createThumbnailFromBytes 从字节数组创建缩略图
// 参数:
//   - ctx: 上下文
//   - itemGroup: 所属组
//   - content: 图片内容
//
// 返回值:
//   - string: 缩略图存储路径
//   - error: 生成过程中发生的错误
func (r *AttachmentRepo) createThumbnailFromBytes(ctx context.Context, itemGroup *ent.Group, content []byte) (string, error) {
	// 解码图片
	img, _, err := image.Decode(bytes.NewReader(content))
	if err != nil {
		return "", fmt.Errorf("解码图片失败: %w", err)
	}

	// 计算缩略图尺寸
	bounds := img.Bounds()
	width := bounds.Max.X - bounds.Min.X
	height := bounds.Max.Y - bounds.Min.Y

	// 保持宽高比缩放
	maxSize := r.thumbnail.Width
	if width > height {
		if width > maxSize {
			height = height * maxSize / width
			width = maxSize
		}
	} else {
		if height > maxSize {
			width = width * maxSize / height
			height = maxSize
		}
	}

	// 创建缩略图
	thumbnail := image.NewRGBA(image.Rect(0, 0, width, height))
	draw.CatmullRom.Scale(thumbnail, thumbnail.Bounds(), img, bounds, draw.Over, nil)

	// 编码为WebP格式
	var buf bytes.Buffer
	if err := webp.Encode(&buf, thumbnail, webp.Options{Quality: 85}); err != nil {
		return "", fmt.Errorf("编码WebP失败: %w", err)
	}

	// 生成缩略图文件名
	thumbnailID := uuid.New()
	thumbnailPath := "attachments/" + thumbnailID.String() + "-thumb.webp"

	// 上传到存储
	bucket, err := blob.OpenBucket(ctx, r.GetConnString())
	if err != nil {
		return "", fmt.Errorf("打开存储桶失败: %w", err)
	}
	defer bucket.Close()

	if err := bucket.WriteAll(ctx, thumbnailPath, buf.Bytes(), &blob.WriterOptions{
		ContentType: "image/webp",
	}); err != nil {
		return "", fmt.Errorf("上传缩略图失败: %w", err)
	}

	return thumbnailPath, nil
}

// CreateThumbnailsForOriginals 为原图创建缩略图但不删除原图
//
// 参数:
//   - ctx: 上下文
//   - groupID: 组ID，如果为空则处理所有组
//   - dryRun: 是否仅预览不实际执行
//
// 返回值:
//   - int: 成功创建缩略图的数量
//   - int: 跳过的数量
//   - error: 创建过程中发生的错误
func (r *AttachmentRepo) CreateThumbnailsForOriginals(ctx context.Context, groupID uuid.UUID, dryRun bool) (int, int, error) {
	log.Info().
		Bool("dryRun", dryRun).
		Str("groupID", groupID.String()).
		Msg("开始为原图创建缩略图（保留原图）")

	// 查询需要创建缩略图的原图
	query := r.db.Attachment.Query()
	if groupID != uuid.Nil {
		query = query.Where(attachment.HasItemWith(item.HasGroupWith(group.ID(groupID))))
	}

	// 查找所有原图附件（包括已经有缩略图的）
	attachments, err := query.
		Where(
			attachment.TypeEQ(attachment.TypePhoto),
		).
		WithItem(func(iq *ent.ItemQuery) {
			iq.WithGroup()
		}).
		All(ctx)
	if err != nil {
		return 0, 0, fmt.Errorf("查询附件失败: %w", err)
	}

	var created, skipped int
	const batchSize = 50 // 每批处理50个文件

	for i := 0; i < len(attachments); i += batchSize {
		end := i + batchSize
		if end > len(attachments) {
			end = len(attachments)
		}

		batch := attachments[i:end]
		log.Info().
			Int("batchStart", i+1).
			Int("batchEnd", end).
			Int("batchSize", len(batch)).
			Msg("处理批次")

		for _, attachment := range batch {
			select {
			case <-ctx.Done():
				log.Info().
					Int("created", created).
					Int("skipped", skipped).
					Msg("创建被中断")
				return created, skipped, ctx.Err()
			default:
				// 继续处理
			}

			// 检查文件是否存在
			bucket, err := blob.OpenBucket(ctx, r.GetConnString())
			if err != nil {
				log.Err(err).Str("attachmentID", attachment.ID.String()).Msg("打开存储桶失败")
				continue
			}

			// 读取原文件
			reader, err := bucket.NewReader(ctx, attachment.Path, nil)
			if err != nil {
				log.Err(err).Str("attachmentID", attachment.ID.String()).Msg("读取原文件失败")
				bucket.Close()
				continue
			}

			// 检测文件类型
			content, err := io.ReadAll(reader)
				reader.Close()
				bucket.Close()

			if err != nil {
				log.Err(err).Str("attachmentID", attachment.ID.String()).Msg("读取文件内容失败")
				continue
			}

			contentType := http.DetectContentType(content[:min(512, len(content))])
			if !isImageFile(contentType) {
				log.Info().
					Str("attachmentID", attachment.ID.String()).
					Str("contentType", contentType).
					Msg("跳过非图片文件")
				skipped++
				continue
			}

			// 检查是否已有缩略图（包括数据库记录和实际文件）
			thumbnailExists := false
			
			// 1. 检查数据库中是否有缩略图记录
			thumbnail, err := attachment.QueryThumbnail().Only(ctx)
			if err == nil && thumbnail != nil {
				// 2. 检查实际缩略图文件是否存在
				bucket, err := blob.OpenBucket(ctx, r.GetConnString())
				if err == nil {
					_, err := bucket.NewReader(ctx, thumbnail.Path, nil)
					bucket.Close()
					if err == nil {
						// 数据库记录和实际文件都存在
						thumbnailExists = true
						log.Info().
							Str("attachmentID", attachment.ID.String()).
							Str("title", attachment.Title).
							Str("thumbnailPath", thumbnail.Path).
							Msg("跳过已有缩略图（数据库记录和文件都存在）")
					} else {
						// 数据库记录存在但文件不存在，需要重新生成
						log.Warn().
							Str("attachmentID", attachment.ID.String()).
							Str("title", attachment.Title).
							Str("thumbnailPath", thumbnail.Path).
							Msg("缩略图文件不存在，将重新生成")
						
						// 删除无效的缩略图记录
						if err := r.db.Attachment.DeleteOne(thumbnail).Exec(ctx); err != nil {
							log.Err(err).Msg("删除无效缩略图记录失败")
						}
					}
				}
			}

			if thumbnailExists {
				skipped++
				continue
			}

			if dryRun {
				log.Info().
					Str("attachmentID", attachment.ID.String()).
					Str("title", attachment.Title).
					Msg("预览：将为此附件创建缩略图")
				created++
				continue
			}

			// 创建缩略图
			thumbnailPath, err := r.createThumbnailFromBytes(ctx, attachment.QueryItem().FirstX(ctx).QueryGroup().FirstX(ctx), content)
			if err != nil {
				log.Err(err).Str("attachmentID", attachment.ID.String()).Msg("创建缩略图失败")
				continue
			}

			// 创建缩略图记录（不删除原图）
				err = func() error {
					tx, err := r.db.Tx(ctx)
					if err != nil {
						return fmt.Errorf("开启事务失败: %w", err)
					}
					defer tx.Rollback()

					// 获取关联的item
					item, err := attachment.QueryItem().First(ctx)
					if err != nil {
						return fmt.Errorf("获取关联项目失败: %w", err)
					}

					// 创建新的缩略图附件记录
					thumbnail, err := tx.Attachment.Create().
						SetPath(thumbnailPath).
						SetTitle(attachment.Title + " (缩略图)").
						SetType("thumbnail").
						SetMimeType("image/webp").
						SetItem(item).
						Save(ctx)
					if err != nil {
						return fmt.Errorf("创建缩略图记录失败: %w", err)
					}

					// 设置缩略图与原图的关联 - 使用事务连接
					_, err = tx.Attachment.UpdateOne(attachment).
						SetThumbnail(thumbnail).
						Save(ctx)
					if err != nil {
						return fmt.Errorf("更新原图缩略图关联失败: %w", err)
					}

					return tx.Commit()
				}()

			if err != nil {
				log.Err(err).Str("attachmentID", attachment.ID.String()).Msg("创建缩略图失败")
				continue
			}

			created++
			log.Info().
				Str("attachmentID", attachment.ID.String()).
				Str("title", attachment.Title).
				Str("thumbnailPath", thumbnailPath).
				Msg("成功为原图创建缩略图")
		}

		// 每批处理完后释放内存
		if !dryRun {
			runtime.GC()
		}
	}

	log.Info().
		Int("created", created).
		Int("skipped", skipped).
		Int("total", len(attachments)).
		Msg("为原图创建缩略图完成")

	return created, skipped, nil
}
